﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;

public partial class DataExcelInOfNPOI
{
    private static bool CheckTable(IRow ColumnsNameRow, Dictionary<string, SqlMapModel> ColumnsMap, out DataTable datatable, out Dictionary<string, Type> dic)
    {
        dic = new Dictionary<string, Type>();
        datatable = new DataTable();
        Regex regx = new Regex(@"\s");
        bool flag = false;
        if (!xlstop(ColumnsNameRow))
        {
            return false;
        }
        if (ColumnsNameRow.LastCellNum != ColumnsMap.Count)
        {
            return false;
        }
        for (int i = 0; i < ColumnsNameRow.LastCellNum; i++)
        {
            string tValue = regx.Replace(ColumnsNameRow.GetCell(i).ToString(), "");
            if (Regex.IsMatch(tValue, @"^\*"))
            {
                tValue = tValue.Remove(0, 1);
            }
            if (ColumnsMap.ContainsKey(tValue))
            {
                if (!flag)
                {
                    flag = true;
                }
                SqlMapModel SqlColumn = ColumnsMap[tValue];
                datatable.Columns.Add(SqlColumn.SqlColumnName, typeof(string));
                dic.Add(SqlColumn.SqlColumnName, SqlColumn.SqlDataType);
            }
            else
            {
                dic = new Dictionary<string, Type>();
                datatable = new DataTable();
                return false;
            }
        }
        return true;
    }

    private static bool CheckTable(IRow ColumnsNameRow, Dictionary<string, SqlMapModel> ColumnsMap, out DataTable datatable, out Dictionary<string, Type> dic, ref string err)
    {
        dic = new Dictionary<string, Type>();
        datatable = new DataTable();
        Regex regx = new Regex(@"\s");
        bool flag = false;
        err = "";
        if (xlstop(ColumnsNameRow))
        {
            if (ColumnsNameRow.LastCellNum == ColumnsMap.Count)
            {
                for (int i = 0; i < ColumnsNameRow.LastCellNum; i++)
                {
                    string tValue = regx.Replace(ColumnsNameRow.GetCell(i).ToString(), "");
                    if (Regex.IsMatch(tValue, @"^\*"))
                    {
                        tValue = tValue.Remove(0, 1);
                    }
                    if (ColumnsMap.ContainsKey(tValue))
                    {
                        if (!flag)
                        {
                            flag = true;
                        }
                        SqlMapModel SqlColumn = ColumnsMap[tValue];
                        datatable.Columns.Add(SqlColumn.SqlColumnName, typeof(string));
                        dic.Add(SqlColumn.SqlColumnName, SqlColumn.SqlDataType);
                    }
                    else
                    {
                        err = "系统发现异常列名(" + tValue + ")";
                        dic = new Dictionary<string, Type>();
                        datatable = new DataTable();
                        return false;
                    }
                }
                return true;
            }
            StringBuilder Sb = new StringBuilder();
            if (ColumnsNameRow.LastCellNum > ColumnsMap.Count)
            {
                Sb.AppendFormat("您选择的文件的列{0},多出系统要求({1}){2}列，参考列{3}", new object[] { ColumnsNameRow.LastCellNum, ColumnsMap.Count, ColumnsNameRow.LastCellNum - ColumnsMap.Count, "{" });
                for (int i = 0; i < ColumnsNameRow.LastCellNum; i++)
                {
                    ICell icell = ColumnsNameRow.GetCell(i);
                    string tValue = regx.Replace(icell.ToString(), "");
                    if (Regex.IsMatch(tValue, @"^\*"))
                    {
                        tValue = tValue.Remove(0, 1);
                    }
                    if (!ColumnsMap.ContainsKey(tValue))
                    {
                        Sb.AppendFormat("{0},", icell.ToString());
                    }
                }
                err = string.Format("{0}{1};", Sb.ToString().Trim(new char[] { ',' }), "}");
            }
            else
            {
                Sb.AppendFormat("您选择的文件的({0}列),有{2}列系统要求({1})的列未提供，参考列{3}", new object[] { ColumnsNameRow.LastCellNum, ColumnsMap.Count, Math.Abs((int) (ColumnsNameRow.LastCellNum - ColumnsMap.Count)), "{" });
                for (int i = 0; i < ColumnsNameRow.LastCellNum; i++)
                {
                    string tValue = regx.Replace(ColumnsNameRow.GetCell(i).ToString(), "");
                    if (Regex.IsMatch(tValue, @"^\*"))
                    {
                        tValue = tValue.Remove(0, 1);
                    }
                    if (ColumnsMap.ContainsKey(tValue))
                    {
                        ColumnsMap.Remove(tValue);
                    }
                    foreach (string k in ColumnsMap.Keys)
                    {
                        Sb.AppendFormat("{0},", k);
                    }
                }
                err = string.Format("{0}{1};", Sb.ToString().Trim(new char[] { ',' }), "}");
            }
        }
        return false;
    }

    public static bool CopyDataIntoSql(string ConnectionString, DataTable datatable, string SqltableName)
    {
        try
        {
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
            for (int i = 0; i < datatable.Columns.Count; i++)
            {
                sqlbulkcopy.ColumnMappings.Add(datatable.Columns[i].ColumnName, datatable.Columns[i].ColumnName);
            }
            sqlbulkcopy.WriteToServer(datatable);
            return true;
        }
        catch
        {
        }
        return false;
    }

    public static DataTable getExcelDataTable(string filePath, Dictionary<string, SqlMapModel> ColumnsMap)
    {
        DataTable datatable = null;
        if (WriteData(getWorkSheet(filePath).GetSheetAt(0), ColumnsMap, ref datatable))
        {
            datatable.Columns.Add("Error", typeof(string));
        }
        return datatable;
    }

    public static DataTable getExcelDataTable(string filePath, Dictionary<string, SqlMapModel> ColumnsMap, ref string err)
    {
        DataTable datatable = null;
        if (WriteData(getWorkSheet(filePath).GetSheetAt(0), ColumnsMap, ref datatable, ref err))
        {
            datatable.Columns.Add("Error", typeof(string));
        }
        return datatable;
    }

    private static HSSFWorkbook getWorkSheet(string FilePath)
    {
        return new HSSFWorkbook(File.OpenRead(FilePath));
    }

    private static bool WriteData(ISheet sheet, Dictionary<string, SqlMapModel> ColumnsMap, ref DataTable datatable)
    {
        bool write = false;
        datatable = new DataTable();
        bool writestart = false;
        Dictionary<string, Type> dic = new Dictionary<string, Type>();
        for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
        {
            IRow nowRow = sheet.GetRow(rowIndex);
            DataRow dr = datatable.NewRow();
            if (nowRow != null)
            {
                if (!writestart)
                {
                    writestart = CheckTable(nowRow, ColumnsMap, out datatable, out dic);
                }
                else
                {
                    bool row_emty = true;
                    for (int cellIndex = 0; cellIndex < nowRow.LastCellNum; cellIndex++)
                    {
                        ICell nowCell = nowRow.GetCell(cellIndex);
                        if (row_emty)
                        {
                            row_emty = (nowCell == null) || string.IsNullOrEmpty(nowCell.ToString());
                        }
                        if (nowCell != null)
                        {
                            if (!write)
                            {
                                write = true;
                            }
                            if (dic[datatable.Columns[cellIndex].ColumnName] == typeof(DateTime))
                            {
                                try
                                {
                                    dr[cellIndex] = nowCell.DateCellValue.ToShortDateString();
                                }
                                catch
                                {
                                    dr[cellIndex] = nowCell;
                                }
                            }
                            else
                            {
                                dr[cellIndex] = nowCell;
                            }
                        }
                        else
                        {
                            dr[cellIndex] = nowCell;
                        }
                    }
                    if (!row_emty)
                    {
                        datatable.Rows.Add(dr);
                    }
                }
            }
        }
        return write;
    }

    private static bool WriteData(ISheet sheet, Dictionary<string, SqlMapModel> ColumnsMap, ref DataTable datatable, ref string err)
    {
        bool write = false;
        datatable = new DataTable();
        bool writestart = false;
        Dictionary<string, Type> dic = new Dictionary<string, Type>();
        for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
        {
            IRow nowRow = sheet.GetRow(rowIndex);
            DataRow dr = datatable.NewRow();
            if (nowRow != null)
            {
                if (!writestart)
                {
                    writestart = CheckTable(nowRow, ColumnsMap, out datatable, out dic, ref err);
                    if (!string.IsNullOrEmpty(err))
                    {
                        return false;
                    }
                }
                else
                {
                    bool row_emty = true;
                    for (int cellIndex = 0; cellIndex < nowRow.LastCellNum; cellIndex++)
                    {
                        ICell nowCell = nowRow.GetCell(cellIndex);
                        if (row_emty)
                        {
                            row_emty = (nowCell == null) || string.IsNullOrEmpty(nowCell.ToString());
                        }
                        if (nowCell != null)
                        {
                            if (!write)
                            {
                                write = true;
                            }
                            if (dic[datatable.Columns[cellIndex].ColumnName] == typeof(DateTime))
                            {
                                try
                                {
                                    dr[cellIndex] = nowCell.DateCellValue;
                                }
                                catch
                                {
                                    dr[cellIndex] = nowCell;
                                }
                            }
                            else
                            {
                                dr[cellIndex] = nowCell;
                            }
                        }
                        else
                        {
                            dr[cellIndex] = nowCell;
                        }
                    }
                    if (!row_emty)
                    {
                        datatable.Rows.Add(dr);
                    }
                }
            }
        }
        return write;
    }

    private static bool xlstop(IRow ColumnsNameRow)
    {
        Regex regx = new Regex(@"\s");
        for (int i = 0; i < ColumnsNameRow.LastCellNum; i++)
        {
            ICell icell = ColumnsNameRow.GetCell(i);
            if (string.IsNullOrEmpty(regx.Replace(icell.ToString(), "")))
            {
                return false;
            }
        }
        return true;
    }
}

